package database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseConnection {

	// Database fields
	public String cat = "";
	private SQLiteDatabase database;
	private SQLiteHelper dbHelper;
	// the columns that will be fetched from the DB
	private String[] allColumns = { SQLiteHelper.COLUMN_ID,
			SQLiteHelper.COLUMN_DATE, SQLiteHelper.COLUMN_EXPENSE,
			SQLiteHelper.COLUMN_CATEGORY, SQLiteHelper.COLUMN_TYPE,
			SQLiteHelper.COLUMN_COST };

	// Create a DBhelper object
	public DatabaseConnection(Context context) {
		dbHelper = new SQLiteHelper(context);
	}

	// get a writable DB object from the helper
	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	// method to save data
	public void addData(String date, String expense, String category,
			String type, String cost) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COLUMN_DATE, date);
		values.put(SQLiteHelper.COLUMN_EXPENSE, expense);
		values.put(SQLiteHelper.COLUMN_CATEGORY, category);
		values.put(SQLiteHelper.COLUMN_TYPE, type);
		values.put(SQLiteHelper.COLUMN_COST, cost);
		database.insert(SQLiteHelper.TABLE_EXPENCES, null, values);

	}

	public int expenseCalc(String category) {
		int sum = 0;
		int sumIncome = 0;
		int sumExpense = 0;

		Cursor cursor = database.rawQuery("SELECT "
				+ SQLiteHelper.COLUMN_COST + " FROM "
				+ SQLiteHelper.TABLE_EXPENCES + " WHERE "
				+ SQLiteHelper.COLUMN_CATEGORY + "=\"" + category + "\";",
				null);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			sumIncome += cursor.getInt(0);
			cursor.moveToNext();
		}
		Cursor cursor2 = database.rawQuery("SELECT "
				+ SQLiteHelper.COLUMN_COST + " FROM "
				+ SQLiteHelper.TABLE_EXPENCES + " WHERE "
				+ SQLiteHelper.COLUMN_CATEGORY + " <> \"" + category + "\";",
				null);
		cursor2.moveToFirst();

		while (!cursor2.isAfterLast()) {
			sumExpense += cursor2.getInt(0);
			cursor2.moveToNext();
		}
		cursor2.close();
		
		sum = sumIncome - sumExpense;
		return sum;
	}
	
	public int sumOfCosts(String category) {
		int sum = 0;

		Cursor cursor = database.rawQuery("SELECT "
				+ SQLiteHelper.COLUMN_COST + " FROM "
				+ SQLiteHelper.TABLE_EXPENCES + " WHERE "
				+ SQLiteHelper.COLUMN_CATEGORY + "=\"" + category + "\";",
				null);
		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			sum += cursor.getInt(0);
			cursor.moveToNext();
		}
		cursor.close();
		return sum;
	}

	// method to get all data
	public ArrayList<String> getAllData() {
		ArrayList<String> strList = new ArrayList<String>();
		Cursor cursor = database.query(SQLiteHelper.TABLE_EXPENCES,
				allColumns, null, null, null, null, null);
		cursor.moveToFirst();
		// the while-loop just collects two of the columns
		while (!cursor.isAfterLast()) {
			String strDate = cursor.getString(1);
			strList.add(strDate);
			String strName = cursor.getString(2);
			strList.add(strName);
			String strCat = cursor.getString(3);
			strList.add(strCat);
			String strType = cursor.getString(4);
			strList.add(strType);
			String strCost = cursor.getString(5);
			strList.add(strCost);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		cursor.close();
		return strList;
	}
}
